﻿/*
 * http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
 * Zdefiniuj komponenty odpowiedzialne za komunikację z bazą danych wykorzystując:
 * a. Kontrolki DataReader
 * b. Kontrolki DataAdapter oraz DataSet
 * 
 */

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;

namespace WebApp
{

    public class MyData:IDAO
    {

        SqlConnection MyConnection;

        public MyData()
        {
            MyConnection = new SqlConnection();

            string con = "Data Source=" + ConfigurationSettings.AppSettings["server"].ToString() + ";";
            con += "Integrated Security=SSPI;";
            con += "Initial Catalog=" + ConfigurationSettings.AppSettings["db"].ToString() + ";";

            MyConnection.ConnectionString = con;     
        }

        List<country> IDAO.getCountry()
        {
            // Data Reader
            MyConnection.Open();

            SqlCommand MyCommand = new SqlCommand("SELECT [id], [name], [description] FROM [projekt].[dbo].[countrys]", MyConnection);

            SqlDataReader reader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
            
            List<country> list = new List<country>();

            while (reader.Read())
            {
                country c = new country();
                c.id = reader.GetInt32(0);
                c.name = reader.GetString(1);
                c.description = reader.GetString(2);
                list.Add(c);
            }

            MyConnection.Close();
            return list;
        }

        country IDAO.getCountry(int id)
        {
            MyConnection.Open();

            SqlCommand MyCommand = new SqlCommand("SELECT [id], [name], [description] FROM [projekt].[dbo].[countrys] WHERE [id] = @Id", MyConnection);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@Id";
            param.Value = id;
            MyCommand.Parameters.Add(param);
            SqlDataReader reader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

            if (reader.Read())
            {
                country c = new country();
                c.id = reader.GetInt32(0);
                c.name = reader.GetString(1);
                c.description = reader.GetString(2);
                MyConnection.Close();
                return c;
            }
            else
            {
                MyConnection.Close();
                return null;
            }
        }

        trip IDAO.getTrip(int id)
        {
            MyConnection.Open();

            SqlCommand MyCommand = new SqlCommand("SELECT [id], [name], [id_country], [price], [places_count], [dead_line], [start_date], [end_date], [description], [status] FROM [projekt].[dbo].[trips] WHERE [id] = @Id", MyConnection);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@Id";
            param.Value = id;
            MyCommand.Parameters.Add(param);
            SqlDataReader reader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

            if (reader.Read())
            {
                trip c = new trip();
                c.id = reader.GetInt32(0);
                c.name = reader.GetString(1);
                c.id_country = reader.GetInt32(2);
                c.price = reader.GetDecimal(3);
                c.places_count = reader.GetInt32(4);
                c.dead_line = reader.GetInt32(5);
                c.start_date = reader.GetDateTime(6);
                c.end_date = reader.GetDateTime(7);
                c.description = reader.GetString(8);
                c.status = reader.GetInt32(9);
                MyConnection.Close();
                return c;
            }
            else
            {
                MyConnection.Close();
                return null;
            }
        }

        List<trip> IDAO.getTrips(int min_price, int max_price, int country, DateTime date_begin, DateTime date_end)
        {
            List<trip> trips = new List<trip>();
            MyConnection.Open();

            SqlCommand MyCommand = new SqlCommand("SELECT [id], [name], [id_country], [price], [places_count], [dead_line], [start_date], [end_date], [description], [status] FROM [projekt].[dbo].[trips] WHERE (([price] > @minPrice AND [price] < @maxPrice) OR [price] = @minPrice OR [price] = @maxPrice) AND ([id_country] = @idCountry) AND ([start_date] > @dateBegin OR [start_date]=@dateBegin) AND ([start_date]<@dateEnd OR [start_date]=@dateEnd) AND ([end_date] > @dateBegin OR [end_date]=@dateBegin) AND ([end_date]<@dateEnd OR [end_date]=@dateEnd)", MyConnection);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@minPrice";
            param.Value = min_price;
            MyCommand.Parameters.Add(param);
            param = new SqlParameter();
            param.ParameterName = "@maxPrice";
            param.Value = max_price;
            MyCommand.Parameters.Add(param);
            param = new SqlParameter();
            param.ParameterName = "@idCountry";
            param.Value = country;
            MyCommand.Parameters.Add(param);
            param = new SqlParameter();
            param.ParameterName = "@dateBegin";
            param.Value = date_begin;
            MyCommand.Parameters.Add(param);
            param = new SqlParameter();
            param.ParameterName = "@dateEnd";
            param.Value = date_end;
            MyCommand.Parameters.Add(param);
            
            SqlDataReader reader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

            while (reader.Read())
            {
                trip c = new trip();
                c.id = reader.GetInt32(0);
                c.name = reader.GetString(1);
                c.id_country = reader.GetInt32(2);
                c.price = reader.GetDecimal(3);
                c.places_count = reader.GetInt32(4);
                c.dead_line = reader.GetInt32(5);
                c.start_date = reader.GetDateTime(6);
                c.end_date = reader.GetDateTime(7);
                c.description = reader.GetString(8);
                c.status = reader.GetInt32(9);
                trips.Add(c);
            }
            MyConnection.Close();
            return trips;
        }

        user IDAO.checkLogin(string username, string password)
        {
            MyConnection.Open();

            SqlCommand MyCommand = new SqlCommand("SELECT [id], [login], [rights], [password], [id_person], [id_company], [email] FROM [projekt].[dbo].[users] WHERE [login] = @login AND [password]=@password", MyConnection);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@login";
            param.Value = username;
            MyCommand.Parameters.Add(param);
            param = new SqlParameter();
            param.ParameterName = "@password";
            param.Value = password;
            MyCommand.Parameters.Add(param);
            SqlDataReader reader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

            if (reader.Read())
            {
                user c = new user();
                c.id = reader.GetInt32(0);
                c.login = reader.GetString(1);
                c.rights = reader.GetString(2);
                c.password = reader.GetString(3);
                c.id_person = reader.GetInt32(4);
                c.id_company = reader.GetInt32(5);
                c.email = reader.GetString(6);
                MyConnection.Close();
                return c;
            }
            else
            {
                MyConnection.Close();
                return null;
            }
        }

        List<reservation> IDAO.getReservations(int person)
        {
            List<reservation> reserwations = new List<reservation>();
            MyConnection.Open();

            SqlCommand MyCommand = new SqlCommand("SELECT [id], [id_trip], [id_person], [id_company], [price], [reservation_date], [cancel_date], [persons_count], [status] FROM [projekt].[dbo].[reservations] WHERE [id_person] = @person", MyConnection);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@person";
            param.Value = person;
            MyCommand.Parameters.Add(param);
                     
            SqlDataReader reader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

            while (reader.Read())
            {
                reservation c = new reservation();
                c.id = reader.GetInt32(0);
                c.id_trip = reader.GetInt32(1);
                c.id_person = reader.GetInt32(2);
                c.id_company = reader.GetInt32(3);
                c.price = reader.GetDecimal(4);
                c.reservation_date = reader.GetDateTime(5);
                c.cancel_date = reader.GetDateTime(6);
                c.persons_count = reader.GetInt32(7);
                c.status = reader.GetInt32(8);
                reserwations.Add(c);
            }
            MyConnection.Close();
            return reserwations;
        }

        int IDAO.addUser(string login, string password, string email)
        {
            MyConnection.Open();
            SqlCommand MyCommand = new SqlCommand("INSERT INTO [projekt].[dbo].[users] ([id_person],[id_company],[login],[password],[rights],[email])  VALUES (NULL,NULL,@login, @password,'1111111',@email);  SELECT NewID = SCOPE_IDENTITY()", MyConnection);
            MyCommand.Parameters.Add("@login", SqlDbType.VarChar).Value = login;
            MyCommand.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
            MyCommand.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
            SqlDataReader dataReader = MyCommand.ExecuteReader();

            int id = -1;

            if (dataReader.HasRows)
            {
                dataReader.Read();
                id = Convert.ToInt32(dataReader["NewID"]);
            }
            dataReader.Close();

            if (MyCommand.ExecuteNonQuery() > 0)
            {
                SqlCommand MyCommand2 = new SqlCommand("INSERT INTO [projekt].[dbo].[persons] ([id_user] ,[name] ,[last_name],[street] ,[postal_code] ,[city] ,[building_number],[flat_number] ,[phone]) VALUES  (@id	,@login,''	,''	,''	,''	,''	,''	,'');  SELECT NewID = SCOPE_IDENTITY()", MyConnection);
                MyCommand2.Parameters.Add("@login", SqlDbType.VarChar).Value = login;
                MyCommand2.Parameters.Add("@Id", SqlDbType.Int).Value = id;
                dataReader = MyCommand2.ExecuteReader();

                int id2 = -1;

                if (dataReader.HasRows)
                {
                    dataReader.Read();
                    id2 = Convert.ToInt32(dataReader["NewID"]);
                }
                dataReader.Close();
                
                SqlCommand MyCommand3 = new SqlCommand("UPDATE [projekt].[dbo].[users] SET [id_person]=@idP WHERE [id]=@id", MyConnection);
                MyCommand3.Parameters.Add("@idP", SqlDbType.Int).Value = id2;
                MyCommand3.Parameters.Add("@id", SqlDbType.Int).Value = id;
                MyCommand.ExecuteNonQuery();
            }
            MyConnection.Close();
            return id;
        }

        void IDAO.addReservation(int trip_id, int person_id, int company_id, int person_count)
        {
            MyConnection.Open();

            SqlCommand MyCommand = new SqlCommand(" addReservations @id,@id2,@id3,@id4 ", MyConnection);
            MyCommand.Parameters.Add("@id", SqlDbType.Int).Value = trip_id;
            MyCommand.Parameters.Add("@id2", SqlDbType.Int).Value = person_id;
            MyCommand.Parameters.Add("@id3", SqlDbType.Int).Value = company_id;
            MyCommand.Parameters.Add("@id4", SqlDbType.Int).Value = person_count;
            MyCommand.ExecuteNonQuery();
            MyConnection.Close();
        }
    }
}